from datetime import datetime

import xlwt
import csv


def open_csv():
    with open('D:/magnific/HRDU2263-25-E02023AUG14190304_dlogTDO.csv', 'r') as f:
        reader = csv.reader(f)
        start_index = 35
        finish_index = 13686
        # finish_index = 40
        data_list = []
        data_list_for_dic = []
        y_coordinate = []
        x_coordinate = []
        data_dict = {}
        count = 0
        for i, row in enumerate(reader):
            if i < start_index:
                continue
            if i >= finish_index:
                break
            y_coordinate.append(int(row[4]))
            x_coordinate.append(int(row[3]))
            count += 1
            # dataList.append(row[3] + row[4])
            # dataList.append(row[2])
            data_list_for_dic.append(row[2:5])
        for i in range(len(data_list_for_dic)):
            data_dict[data_list_for_dic[i][1] + data_list_for_dic[i][2]] = data_list_for_dic[i][0]
        return y_coordinate, x_coordinate, data_dict


# Excel模板
def excel_template():
    # Excel文件名, sheet页名, 表格标题
    file_name_xls = "D:/magnific/test.xls"
    sheet_name_xls = "sheet"
    y_coordinate, x_coordinate, data_dict = open_csv()
    # value_title = [['标题1', '标题2'], ['100', '1000']]

    write_excel_xls(file_name_xls, sheet_name_xls, y_coordinate, x_coordinate, data_dict)


def write_excel_xls(path, sheet_name, y_coordinate, x_coordinate, data_dict):
    # index = len(x_coordinate)  # 获取需要写入的列数
    print("xls格式表格开始写入！")
    # time_1_struct = datetime.strptime(time_1, "%Y-%m-%d %H:%M:%S")
    # time_2_struct = datetime.strptime(time_2, "%Y-%m-%d %H:%M:%S")
    workbook = xlwt.Workbook()  # 新建一个工作簿
    # print(data_dict)
    sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)  # 新建一个工作表
    for x in x_coordinate:
        for y in y_coordinate:
            sheet.write(y, x, data_dict.get(str(x) + str(y), None))
    #
    # for i in range(0, index):
    #     for j in range(0, len(y_coordinate)):
    #         sheet.write(i, j, data_dict[str(x_coordinate) + str(y_coordinate)])  # 像表格中写入数据（对应的行和列）
    workbook.save(path)  # 保存工作簿

    print("xls格式表格写入数据成功！")
    print("总共耗时")
    print(path)


if __name__ == "__main__":
    # print(GetID())
    excel_template()
